package com.sjsu.CMPE226.partitioned.jdbc.repo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sjsu.CMPE226.normalized.jdbc.data.Observation;

public class ObservationMapper {

	public static List<Observation> decodeAllObservations(ResultSet rs) {
		List<Observation> obs = new ArrayList<Observation>();

		try {
			while (rs.next()) {
				Observation o = new Observation();
				o.setObservationId(rs.getLong("Observation_Id"));
				o.setStationId(rs.getString("Station_Id"));
				o.setObservationsDateTime(rs.getDate("ObservationsDateTime"));
				o.setTemperature(rs.getFloat("Temperature"));
				o.setWindSpeed(rs.getFloat("Wind_Speed"));
				o.setDirection(rs.getFloat("Direction"));
				o.setGust(rs.getFloat("Gust"));
				o.setSeaLevelPressure(rs.getFloat("Sea_Level_Pressure"));
				o.setAltimeter(rs.getFloat("Altimeter"));
				o.setDewPoint(rs.getFloat("Dew_Point"));
				o.setRelativeHumidity(rs.getFloat("Relative_Humidity"));
				o.setWeatherCode(rs.getFloat("Numeric_Gempak_Weather_Code"));
				o.setP24I(rs.getFloat("P24I"));

				obs.add(o);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return obs;
	}

	public static PreparedStatement prepareSelectAllObservationsForDate(Connection con)
			throws Exception {
		PreparedStatement psObs = con
				.prepareStatement("Select * from observations where ObservationsDateTime = '2012-10-06'");
		return psObs;
	}

	public static PreparedStatement prepareSelectAllObservationsForState(
			Connection con) throws Exception {
		PreparedStatement psObs = con
				.prepareStatement("Select S.Station_Id, S.Station_Name, "
						+ "O.*, max(O.Temperature), "
						+ "min(O.Temperature), max(O.Relative_Humidity), "
						+ "min(O.Relative_Humidity), max(O.Dew_Point), "
						+ "min(O.Dew_Point), max(O.Gust), O.P24I, S.Elevation from "
						+ "observations O, stations_info S "
						+ "where S.Station_Id = O.Station_Id "
						+ "and S.State = 'CA' " + "group by S.Station_Name");

		return psObs;
	}

	public static PreparedStatement prepareSelectAllObservationsForNetwork(
			Connection con) throws Exception {
		PreparedStatement psObs = con
				.prepareStatement("Select S.Station_Id, S.Station_Name, N.Network_Name, "
						+ "O.*, max(O.Temperature), "
						+ "min(O.Temperature), max(O.Relative_Humidity), "
						+ "min(O.Relative_Humidity), max(O.Dew_Point), "
						+ "min(O.Dew_Point), max(O.Gust), O.P24I, S.Elevation from "
						+ "observations O, stations_info S, mesowest_network_info N "
						+ "where S.Station_Id = O.Station_Id and S.Network_Id = N.Network_Id "
						+ "and N.Network_Name = 'APRSWXNET/CWOP' "
						+ "group by S.Station_Name");

		return psObs;
	}

	public static PreparedStatement prepareSelectAllObservationsForNetworkAndProvider(
			Connection con) throws Exception {
		PreparedStatement psObs = con
				.prepareStatement("Select S.Station_Id, S.Station_Name, "
						+ "N.Network_Name, P.Provider_Name, O.*, max(O.Temperature), "
						+ "min(O.Temperature), max(O.Relative_Humidity), "
						+ "min(O.Relative_Humidity), max(O.Dew_Point), "
						+ "min(O.Dew_Point), max(O.Gust), O.P24I, S.Elevation from "
						+ "observations O, stations_info S, mesowest_network_info N, "
						+ "network_provider_info P where S.Station_Id = O.Station_Id "
						+ "and S.Network_Id = N.Network_Id and S.Primary_Provider_Id = P.Provider_Id "
						+ "and P.Provider_Name = 'National Weather Service' "
						+ "and N.Network_Name = 'NWS/FAA' "
						+ "group by S.Station_Name");

		return psObs;
	}

	public static PreparedStatement prepareUpdateObservationsForSeaLevelPressure(
			Connection con) throws Exception {
		PreparedStatement psObs = con
				.prepareStatement("Update observations O, stations_info S "
						+ "set O.Sea_Level_Pressure = 0 where S.State = 'VA' and "
						+ "O.Sea_Level_Pressure = -9999 "
						+ "and O.Station_Id = S.Station_Id");

		return psObs;
	}

	public static PreparedStatement prepareDeleteObservationsForAltimeter(
			Connection con) throws Exception {
		PreparedStatement psObs = con
				.prepareStatement("Delete from observations USING observations INNER JOIN stations_info "
						+ "ON observations.Station_Id = stations_info.Station_Id where "
						+ "stations_info.Station_Name like 'CW%' and observations.Altimeter = -9999");

		return psObs;
	}
}
